--    Program: 		CAPPOIMP_REQUISITION_HEADER.sql
--    Description:  Import PO Requisition Header information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_requisition_header_id  number;
                    		                 		
   cursor cur_requisition_all is
      select * from CRST_PO_REQUISITION_HEADERS_AL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_requisition_all loop   	
     l_err_msg_tmp := '';
     
     select PO_REQUISITION_HEADERS_S.NEXTVAL 
     into l_requisition_header_id
     FROM DUAL;
     
     update CRST_PO_REQUISITION_HEADERS_AL
     set    requisition_header_id_n = l_requisition_header_id
     where  requisition_header_id = cur.REQUISITION_HEADER_ID;
     
     begin
       -- import data
       insert into PO_REQUISITION_HEADERS_ALL 
       (
          REQUISITION_HEADER_ID      ,
          PREPARER_ID                ,
          LAST_UPDATE_DATE           ,
          LAST_UPDATED_BY            ,
          SEGMENT1                   ,
          SUMMARY_FLAG               ,
          ENABLED_FLAG               ,
          SEGMENT2                   ,
          SEGMENT3                   ,
          SEGMENT4                   ,
          SEGMENT5                   ,
          START_DATE_ACTIVE          ,
          END_DATE_ACTIVE            ,
          LAST_UPDATE_LOGIN          ,
          CREATION_DATE              ,
          CREATED_BY                 ,
          DESCRIPTION                ,
          AUTHORIZATION_STATUS       ,
          NOTE_TO_AUTHORIZER         ,
          TYPE_LOOKUP_CODE           ,
          TRANSFERRED_TO_OE_FLAG     ,
          ATTRIBUTE_CATEGORY         ,
          ATTRIBUTE1                 ,
          ATTRIBUTE2                 ,
          ATTRIBUTE3                 ,
          ATTRIBUTE4                 ,
          ATTRIBUTE5                 ,
          ON_LINE_FLAG               ,
          PRELIMINARY_RESEARCH_FLAG  ,
          RESEARCH_COMPLETE_FLAG     ,
          PREPARER_FINISHED_FLAG     ,
          PREPARER_FINISHED_DATE     ,
          AGENT_RETURN_FLAG          ,
          AGENT_RETURN_NOTE          ,
          CANCEL_FLAG                ,
          ATTRIBUTE6                 ,
          ATTRIBUTE7                 ,
          ATTRIBUTE8                 ,
          ATTRIBUTE9                 ,
          ATTRIBUTE10                ,
          ATTRIBUTE11                ,
          ATTRIBUTE12                ,
          ATTRIBUTE13                ,
          ATTRIBUTE14                ,
          ATTRIBUTE15                ,
          USSGL_TRANSACTION_CODE     ,
          GOVERNMENT_CONTEXT         ,
          REQUEST_ID                 ,
          PROGRAM_APPLICATION_ID     ,
          PROGRAM_ID                 ,
          PROGRAM_UPDATE_DATE        ,
          INTERFACE_SOURCE_CODE      ,
          INTERFACE_SOURCE_LINE_ID   ,
          CLOSED_CODE                ,
          ORG_ID                     ,
          WF_ITEM_TYPE               ,
          WF_ITEM_KEY                ,
          EMERGENCY_PO_NUM           ,
          PCARD_ID                   ,
          APPS_SOURCE_CODE           ,
          CBC_ACCOUNTING_DATE        ,
          CHANGE_PENDING_FLAG        
       )
       values
       (
          l_requisition_header_id        ,
          CAPBRIMP_MAPPING.GetEmployeeMapping(cur.PREPARER_ID),
          cur.LAST_UPDATE_DATE           ,
          CUR.LAST_UPDATED_BY,
          cur.SEGMENT1                   ,
          cur.SUMMARY_FLAG               ,
          cur.ENABLED_FLAG               ,
          cur.SEGMENT2                   ,
          cur.SEGMENT3                   ,
          cur.SEGMENT4                   ,
          cur.SEGMENT5                   ,
          cur.START_DATE_ACTIVE          ,
          cur.END_DATE_ACTIVE            ,
          cur.LAST_UPDATE_LOGIN          ,
          cur.CREATION_DATE              ,
          CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
          cur.DESCRIPTION                ,
          cur.AUTHORIZATION_STATUS       ,
          cur.NOTE_TO_AUTHORIZER         ,
          cur.TYPE_LOOKUP_CODE           ,
          cur.TRANSFERRED_TO_OE_FLAG     ,
          cur.ATTRIBUTE_CATEGORY         ,
          cur.ATTRIBUTE1                 ,
          cur.ATTRIBUTE2                 ,
          cur.ATTRIBUTE3                 ,
          cur.ATTRIBUTE4                 ,
          cur.ATTRIBUTE5                 ,
          cur.ON_LINE_FLAG               ,
          cur.PRELIMINARY_RESEARCH_FLAG  ,
          cur.RESEARCH_COMPLETE_FLAG     ,
          cur.PREPARER_FINISHED_FLAG     ,
          cur.PREPARER_FINISHED_DATE     ,
          cur.AGENT_RETURN_FLAG          ,
          cur.AGENT_RETURN_NOTE          ,
          cur.CANCEL_FLAG                ,
          cur.ATTRIBUTE6                 ,
          cur.ATTRIBUTE7                 ,     
          cur.ATTRIBUTE8                 ,     
          cur.ATTRIBUTE9                 ,     
          cur.ATTRIBUTE10                ,     
          cur.ATTRIBUTE11                ,     
          cur.ATTRIBUTE12                ,     
          cur.ATTRIBUTE13                ,     
          cur.ATTRIBUTE14                ,     
          cur.ATTRIBUTE15                ,     
          cur.USSGL_TRANSACTION_CODE     ,     
          cur.GOVERNMENT_CONTEXT         ,     
          cur.REQUEST_ID                 ,     
          cur.PROGRAM_APPLICATION_ID     ,     
          cur.PROGRAM_ID                 ,     
          cur.PROGRAM_UPDATE_DATE        ,     
          cur.INTERFACE_SOURCE_CODE      ,     
          cur.INTERFACE_SOURCE_LINE_ID   ,     
          cur.CLOSED_CODE                ,     
          CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),     
          cur.WF_ITEM_TYPE               ,     
          cur.WF_ITEM_KEY                ,     
          cur.EMERGENCY_PO_NUM           ,     
          cur.PCARD_ID                   ,     
          cur.APPS_SOURCE_CODE           ,     
          cur.CBC_ACCOUNTING_DATE        ,     
          cur.CHANGE_PENDING_FLAG              
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_REQUISITION_HEADER', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
